	/*
    DESCRIPTION: 
       1. merging Household and personal datasets, then Min Wage
	   2. adjust for UHS waves of surveys
	   3. merge with cpi index data and create real values fro wage and income
	   4. refine the sample following Ge and Yang JEEA, and French et al.
	   5. drop workers observed only once and define treated workers' group 
	   6. identify HH observed only once and with only one observation in the first year they are surveyed
	   7. compute the share of minimum wage within HH
	   8. Merge with County and City datasets 
	   9. prepare variables for regression
	   10. count how many HH observed between 2003/2004 for sharp diff in diff
    
*/


set more off
clear all


*Set the folder where data are
cd ""


use "02-09household data.dta", clear

*generate group identifiers to count the number of groups by county,province
sort dcode hcode
egen dhcode = group(dcode hcode)
order  cityid countyid, before(dcode)
order dhcode, after(hcode)


************************** 1. ******************************
***Merge the household and personal data with minim wage ***
************************************************************
sort dcode hcode year
joinby dcode hcode year using "02-09personal data.dta", unmatched(master)
drop _merge


**monthly min wage merging***
ren dcode county
gen provid  = int(cityid/100)
order provid, after(prov)
joinby county year using "minwage2_county2.dta", unmatched(master)
joinby cityid year using "minwage2_city.dta", unmatched(master) update _merge(_merge1)
joinby provid year using "minwage2_prov.dta", unmatched(master) update _merge(_merge2)
order miniwage mwminiwage lminiwage l2miniwage l3miniwage fminiwage f2miniwage f3miniwage lmwminiwage l2mwminiwage l3mwminiwage fmwminiwage f2mwminiwage f3mwminiwage, after(year)
drop countynm name
drop _merge _merge1 _merge2
drop prov


 
**************************** 2. *************************
*** Adjust the coding of entries between waves of UHS ***
*********************************************************

replace a2=7 if a2==5 & year>2006
replace a2=7 if a2==9 & year>2006
replace a2=5 if a2==4 & year>2006
replace a2=4 if a2==8 & year>2006
replace a2=8 if a2==10 & year>2006                //adjust relationship with head of HH 

*age
replace a7=a7/100 if a7>1000       // adjustment becase in previous vintage of UHS was coded as year of birth
replace a7=int(a7) 
replace a7=year-a7 if year>2006				//adjust member's age

*work experience
replace a10=year-a10 if year>2006
replace a10=0 if year==2007&a10==2007
replace a10=0 if year==2008&a10==2008
replace a10=0 if year==2009&a10==2009       //adjust work experience
count if a10>=a7 & a7!=0 & a10!=.
replace a10=. if a10>=a7 & a7!=0 & a10!=.   //set as missing meaningless values

*migration to urban area
replace a4=year-a4 if year>2006
count if a4>a7 & a7!=0 & a4!=.            // find individuals that migrated before where born (i.e meaningless values)
replace a4=. if a4>a7 & a7!=0 & a4!=.   //set as missing meaningless values

*industry
replace a13=7  if year>2003 & a13==6
replace a13=8  if year>2003 & a13==9
replace a13=9  if year>2003 & a13==10
replace a13=10  if year>2003 & a13==11
replace a13=11  if year>2003 & a13==15
replace a13=6  if year>2003 & a13==14
replace a13=14  if year>2003 & a13==13
replace a13=13  if year>2003 & a13==16
replace a13=13  if year>2003 & a13==18
replace a13=16  if year>2003 & a13==12
replace a13=16  if year>2003 & a13==20
replace a13=15  if year>2003 & a13==19
replace a13=12  if year>2003 & a13==17


***Rename and label variables***
ren a1 member
label var member "a1 code of family members"
ren a2 relhead
label var relhead "a2 relationship with head of HH"
ren a4 migration
label var migration "a4 time from migration to this city or town"
ren a6 gender
replace gender=. if gender==0 | gender==3  //set as missing meaningless values
replace gender=0 if gender ==1   //  0 = male
replace gender=1 if gender==2     // 1 = female
ren a7 age
label var age "a7 age"
ren a8 educ
label var educ "a8 education enrollment"
ren a9 maritstatus
label var maritstatus "a9 marital status"
ren a10 wexper
label var wexper "a10 work experience"
ren a11 hrs
label var hrs "a11 last month hours worked"
ren a12 emplstatus
label var emplstatus "a12 employment status"
ren a13 industry
label var industry "a13 industry"
ren a14 occupation
label var occupation "a14 occupation"
ren a15 wtotinc
label var wtotinc "a15 worker total income"
ren a151 wwage
label var wwage "a151 worker income from wages and salaries"
label variable county "county identifier"
ren a18 wsocialsecc
label var wsocialsecc "a18 worker social security contributions"
order member relhead migration gender age educ maritstatus wexper hrs emplstatus industry occupation wtotinc wwage wsocialsecc, before(b013)
order mwhrp, before(b013)

*number of children within houesehold
gen child=1 if relhead==3
bysort dhcode year: egen children=sum(child)
bysort dhcode: egen nchildren=max(children)

*male child
gen son=1 if relhead==3 & gender==0
bysort dhcode: egen malechild=max(son)

*adult child
gen adultchildren=1 if relhead==3 & age>24
bysort dhcode: egen adultchildhh=max(adultchildren)


******************************* 3. *******************************************
*** Merge with Province level data to get the CPI index and real variables ***
******************************************************************************

*****merge with CPI index data and create real wages and income****
sort provid year
joinby provid year using "02-09prov_cpi.dta", unmatched(master)
drop _merge

*create real annual variables
gen realminw=miniwage*12/cpiindex      // annual real minimum wage in 2002 yuan
gen lrealminw=lminiwage*12/lcpiindex
gen l2realminw=l2miniwage*12/l2cpiindex
gen l3realminw=l3miniwage*12/l3cpiindex
gen frealminw=fminiwage*12/fcpiindex
gen f2realminw=f2miniwage*12/f2cpiindex
gen f3realminw=f3miniwage*12/f3cpiindex

gen effrealmw=mwminiwage*12/cpiindex   // annual real effective minimum wage in 2002 yuan
gen leffrealminw=lminiwage*12/lcpiindex
gen l2effrealminw=l2miniwage*12/l2cpiindex
gen l3effrealminw=l3miniwage*12/l3cpiindex
gen feffrealminw=fminiwage*12/fcpiindex
gen f2effrealminw=f2miniwage*12/f2cpiindex
gen f3effrealminw=f3miniwage*12/f3cpiindex

gen lneffrealmw = ln(effrealmw)
label var lneffrealmw "log annual effective real MW"

gen wnpinc=wwage+a152+a154
label var wnpinc "a151+a152+a154 worker non property income"
gen realwnpinc=wnpinc/cpiindex            // annual real worker non  property income
gen realwtotinc=wtotinc/cpiindex         // annual real worker total income
gen realwwage=wwage/cpiindex            // annual real worker labor wage
gen realwsocialsecc=wsocialsecc/cpiindex   // annual real worker social security contributions
label var realminw "annual real minimum wage, 2002 yuan"
label var effrealmw "annual real effective minimum wage, 2002 yuan"
label var realwtotinc "annual real worker total income"
label var realwwage "annual real worker labor wage"
label var realwsocialsecc "annual real worker social security contributions"
label var miniwage "end-of-year monthly min wage"
label var mwminiwage "effective monthly (monthly average) min wage"
order realminw lrealminw effrealmw realwtotinc realwwage realwsocialsecc, before(member)




******************************* 4. *********************************************************
*** Sample Refinements: following Aaronson_et al_AER Online Appendix_Spending Minimum wage ***
********************************************************************************************
drop if relhead==0  // eliminate meaningless entries

*drop self-employed, students, retirees etc.
tab emplstatus
drop  if emplstatus==11 & realwwage!=0
drop if emplstatus==4 
drop if emplstatus==6 
drop if  emplstatus==8 
drop if  emplstatus==9 
drop if  emplstatus==10 
drop if  emplstatus==12 
drop if  emplstatus==13 
drop if  emplstatus==14 
drop if  emplstatus==15

drop if relhead==8  //  eliminate non-relatives

*confining the sample to the labor force
count if age<16 & age!=.
tab emplstatus if age>59 & gender==1
tab emplstatus if age>54 & gender==2
drop if age<16 & age!=.
drop if age>59 & gender==0
drop if age>54 & gender==1

*drop observations with annual real wage outliers wrt to the min wage
drop if wwage==.
drop if realwwage<0.5*effrealmw & emplstatus!=11
drop if realwwage>45*effrealmw
count

*define worker identifier
sort dhcode member
egen workerid = group(dhcode member gender)
order workerid, after(dhcode)


* drop duplicate id's
duplicates report workerid year
duplicates tag workerid year, gen(duplic)
drop if duplic==1 | duplic==2 | duplic==3


*drop meaningless age
xtset workerid year
sort workerid year
by workerid: gen agec=age-l.age
by workerid: gen yearc=year-l.year
by workerid: gen wrong=1 if agec!=yearc
replace wrong=. if agec==0 | agec==2
count if wrong==1 
drop if wrong==1


*define workers observed only for one period (we may want to keep it since they are 100,000+)
sort workerid year
bysort workerid year: gen wnyrsp = _n == 1   // create a dummy variable with 1 if the first time worker is observed in that year, 0 otherwise
by workerid: replace wnyrsp = sum(wnyrsp)   // sum the 1s and 0s only within worker, hence the first year is observed is coded 1, the second is 2 etc
by workerid: replace wnyrsp = wnyrsp[_N]    //  within worker replaces all values with the highest
label var wnyrsp "worker total number of years in UHS panel"
move wnyrsp year
tab wnyrsp
*drop if wnyrsp==1
count

*create dummy for first time worker observed in the panel
sort workerid year
bysort workerid: gen wfirstobs = _n == 1     //dummy first year worker is observed, 0 otherwise
label var wfirstobs "dummy for the year 1st time the worker is observed"
order wfirstobs, before(year)

*create unemployment dummy identifier first year observed in panel
gen unemplfyr=1 if emplstatus==11 & wfirstobs==1
replace unemplfyr=0 if emplstatus!=11 & emplstatus!=. & wfirstobs==1
bysort workerid: egen unemployedfyr = sum(unemplfyr)   // this gives the unemployed condition observed in the first year for all years after the first year

*unemployed dummy if ever unemployed in panel
gen unempl=1 if emplstatus==11
replace unempl=0 if unempl==. 
bysort workerid: egen unemployed = max(unempl)   // this gives the unemployed condition if eworker was unemployed at least for one year
order unemplfyr unempl unemployedfyr unemployed, before(emplstatus)

*drop abnormaly high increases (>10 folds) or decreases (<10 folds) in real wages from the first to the last observation within worker
sort workerid year
by workerid: gen crw = (realwwage[_N] - realwwage[1])/realwwage[1]
by workerid: gen crw1 = (realwwage[_N] - realwwage[1])/realwwage[1] if unemployedfyr!=1
order crw crw1, before(unemployed)
count if crw>10 & crw!=.
count if crw1>10 & crw1!=.
drop if  crw>10 & crw!=.
count 

*there are no abnormal decreases
by workerid: gen drw = (realwwage[1] - realwwage[_N])/realwwage[1]
by workerid: gen drw1 = (realwwage[1] - realwwage[_N])/realwwage[1] if unemployedfyr!=1
order drw drw1, before(unemployed)
count if drw>10 & drw!=.
count if drw1>10 & drw1!=.

drop crw crw1 drw drw1
count


*** Generate few Variables ****
*******************************

*Family size
ren b21 familysize
label var familysize "Family size"
bysort dhcode year: gen hhsize= _N
label var hhsize "Household Sample Size"
bysort dhcode: replace familysize=hhsize if familysize==0


*House ownership
ren  b24 typehouseown
label var typehouseown "type of house ownership"
gen houseown=1 if typehouseown==3 | typehouseown==4 | typehouseown==5
replace houseown=0 if typehouseown==1 | typehouseown==2 | typehouseown==6
label var houseown "House ownership"
move houseown typehouseown

*define HH with mortgage
ren d967 mrtgagexpend
gen mortgage = mrtgagexpend>0 if !missing(mrtgagexpend)

*define SOE worker
gen SOE=1 if emplstatus==1
replace SOE=0 if SOE==. & emplstatus!=11
label var SOE "SOE employee"

*generate share of SOE workers by county
bysort countyid year: gen SOEsh=sum(SOE)
bysort countyid year: replace SOEsh=SOEsh[_N]
bysort countyid year: replace SOEsh=SOEsh/_N




************************** 5. ***************************************
*** Define treated workers and compute share of minimum wage in HH ***
*********************************************************************

*** count number of worker-years paid below minimum wage in a given year
set more off
sort year workerid
gen wwbmw = 1 if 0.5*effrealmw<realwwage & realwwage<effrealmw & emplstatus!=11
bysort year: replace wwbmw = sum(wwbmw)
bysort year: egen wbmw = max(wwbmw)
by year: sum wbmw

*** count number of workers in a given year
set more off
bysort workerid year: gen nworkers = _n == 1 if emplstatus!=11
bysort year: replace nworkers = sum(nworkers)
bysort year: egen nworkersy = max(nworkers)
by year: sum nworkersy

*** generate proportion of worker-years that are paid below minimum wage
gen pwbmw = wbmw/nworkersy
label var pwbmw "Proportion of workers below MW"
by year: sum pwbmw



***TREATED IN THE INITIAL YEAR***
*********************************
xtset workerid year
*define treated [.5 MW<=x<=1.5 MW] MW workers in the first year of their observation
gen MWworker=1 if 0.5*effrealmw<=realwwage & 1.5*effrealmw>=realwwage & wfirstobs==1
replace MWworker=0 if 1.5*effrealmw<realwwage & wfirstobs==1
bysort workerid: replace MWworker = sum(MWworker) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var MWworker "dummy for MW treated workers up to 1.5 of mw in the first year"
order MWworker, before(member)
tab MWworker

*define treated [.5 MW<=x<=1.2 MW] MW workers in the first year of their observation
sort workerid year
gen MWworker1=1 if 0.5*effrealmw<=realwwage & 1.2*effrealmw>=realwwage & wfirstobs==1
replace MWworker1=0 if 1.2*effrealmw<realwwage & wfirstobs==1
bysort workerid: replace MWworker1 = sum(MWworker1) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var MWworker1 "dummy for MW treated workers up to 1.2 of mw in the first year"
order MWworker1, before(member)
tab MWworker1

*define treated [.5 MW<=x<=1.3 MW] MW workers in the first year of their observation
sort workerid year
gen MWworker13=1 if 0.5*effrealmw<=realwwage & 1.3*effrealmw>=realwwage & wfirstobs==1
replace MWworker13=0 if 1.3*effrealmw<realwwage & wfirstobs==1
bysort workerid: replace MWworker13 = sum(MWworker13) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var MWworker13 "dummy for MW treated workers up to 1.3 of mw in the first year"
order MWworker13, before(member)
tab MWworker13


*define Mid-Income workers [1.5 MW<x<=3 MW] in the first year of their observation
sort workerid year
gen MIworker=1 if 1.5*effrealmw<realwwage & 3*effrealmw>=realwwage & wfirstobs==1
replace MIworker=0 if 3*effrealmw<=realwwage | (1.5*effrealmw>realwwage & 0.5*effrealmw<=realwwage) & wfirstobs==1
bysort workerid: replace MIworker = sum(MIworker) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var MIworker "dummy for Mid Income workers if 1.5 < MW < 3"
order MIworker, before(member)
tab MIworker

*define Mid-Income workers [1.2 MW<x<=3 MW] in the first year of their observation
sort workerid year
gen MIworker1=1 if 1.2*effrealmw<realwwage & 3*effrealmw>=realwwage & wfirstobs==1
replace MIworker1=0 if 3*effrealmw<=realwwage | (1.2*effrealmw>realwwage & 0.5*effrealmw<=realwwage) & wfirstobs==1
bysort workerid: replace MIworker1 = sum(MIworker1) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var MIworker "dummy for Mid Income workers if 1.2 < MW < 3"
order MIworker1, before(member)
tab MIworker1

*define High Income workers [4 MW<x] in the first year of their observation
sort workerid year
gen HIworker=1 if 4*effrealmw<realwwage & wfirstobs==1
replace HIworker=0 if 4*effrealmw>=realwwage & 0.5*effrealmw<=realwwage & wfirstobs==1
bysort workerid: replace HIworker = sum(HIworker) if realwwage>0.5*effrealmw // this gives the dummy 1 for the years after the first if worker is MW treated, 0 otherwise
label var HIworker "dummy for High Income workers"
order HIworker, before(member)
tab HIworker



***TREATED BY YEAR and TREATED EACH YEAR***
******************************************
*define treated [.5 MW<=x<=1.5 MW] MW workers by year
sort workerid year
gen MWworker2=1 if 0.5*effrealmw<=realwwage & 1.5*effrealmw>=realwwage    // generate MW worker by year
replace MWworker2=0 if 1.5*effrealmw<realwwage 
label var MWworker2 "dummy for MW treated workers up to 1.5 of MW by year"
order MWworker2, before(member)
tab MWworker2

*create treated [.5 MW<=x<=1.5 MW] MW workers each year
by workerid: egen MWw2=total(MWworker2) if MWworker2!=.   // cretaes variable with the number of years a worker is in treatment group
order MWw2, before(member)
count if wnyrsp==MWw2
gen MWworker3=1 if wnyrsp==MWw2    // generates MW worker that has been treated each year in panel
replace MWworker3=0 if MWworker3==. & MWworker2!=.
label var MWworker3 "dummy for MW treated workers up to 1.5 of MW in each year"
tab MWworker3

*define treated [.5 MW<=x<=1.2 MW] MW workers by year
sort workerid year
gen MWworker4=1 if 0.5*effrealmw<=realwwage & 1.2*effrealmw>=realwwage    // generate MW worker by year
replace MWworker4=0 if 1.2*effrealmw<realwwage 
label var MWworker4 "dummy for MW treated workers up to 1.2 of MW by year"
order MWworker4, before(member)
tab MWworker4

*create treated [.5 MW<=x<=1.2 MW] MW workers each year
by workerid: egen MWw4=total(MWworker4) if MWworker4!=.    // cretaes variable with the number of years a worker is in treatment group
order MWw4, before(member)
count if wnyrsp==MWw4
gen MWworker5=1 if wnyrsp==MWw4   // generates MW worker that has been treated each year in panel
replace MWworker5=0 if MWworker5==. & MWworker4!=.
label var MWworker5 "dummy for MW treated workers up to 1.2 of MW in each year"
tab MWworker5


***************  6. **************************
*** Identify Households observed only once ***
**********************************************

*Identify HH observed only for one year 
bysort dhcode year: gen hnyrsp = _n == 1   // create a dummy variable 1 for first member in HH each year, 0 otherwise
by dhcode: replace hnyrsp = sum(hnyrsp)     // sum the 1s and 0s only within HH, hence the first year that first member in HH is observed is 1, the second year is 2 etc
by dhcode: replace hnyrsp = hnyrsp[_N]      //  within HH replaces all values with the last to give the HH number of years in the UHS
label var hnyrsp "HH total number of years in UHS panel"
move hnyrsp year
tab hnyrsp
drop if hnyrsp==1

*identify HH with only one observation in the first year they are observed
sort dhcode year
bysort dhcode: gen hfyr = _n == 1                // create a dummy variable 1 for HH in the first year the HH is observed, 0 otherwise
by dhcode year: replace hfyr = sum(hfyr)    // creates dummy for the first year a HH is observed is 1 for all members of the HH and 0 otherwise
by dhcode year: egen honeobsfyr=total(hfyr)         // creates variable giving the sum of 1s within the first year of observation, if more than 1 then HH has more than one member
by dhcode: replace honeobsfyr=sum(honeobsfyr)            // identifier 1 for the following years if single member HH in the first year 
replace honeobsfyr=0 if honeobsfyr!=1                  // creates the dummy for the HH with only 1 observation the first year is observed, and 0 otherwise
label var honeobsfyr "dummy HH with only 1 obs when 1st observed"
label var hfyr "dummy for the 1st year the HH is observed"
order hfyr honeobsfyr, before(year)
tab honeobsfyr 


* drop households having duplicates in the relation with head entry
sort dhcode year relhead
quietly by dhcode year relhead:  gen duplhead = cond(_N==1,0,_n) if relhead<3 & honeobsfyr==0
bysort dhcode: egen dupla=max(duplhead)
drop if dupla==2
drop dupla duplhead
count



********************** 7. ***************************************************
*** compute the shares S of MW within HH First Year worker and HH in panel***
*****************************************************************************

*rank the HH members by real wage by year
sort dhcode year realwwage
bysort dhcode year: egen hmembrank=rank(-realwwage)
label var hmembrank "rank of members by wage, 1=highest"
tab hmembrank

**create total best 2 members income
bysort dhcode year: egen b2inc=total(realwtotinc) if (hmembrank==1 | hmembrank==2)
label var b2inc "Best 2 members total non property income in HH"
gen lnb2inc = ln(b2inc)

*create total HH income 
gen hhrealtotinc=d92/cpiindex
label var hhrealtotinc "HH real Total income, base 2002"

*create total Head and Spouse income
bysort dhcode year: egen HSinc=total(realwtotinc) if relhead<3
label var HSinc "Head and Spouse total non property income in HH"
gen lnHSinc = ln(HSinc)



***Share of HH MW for BEST 2 if treated in the first year observed ****
***********************************************************************

*create number of HH members obsevred in the first year per HH is in UHS
by dhcode year: egen hhmembfyr=total(hfyr)  //creates the total number of members in the HH that are observed in the first year the HH enters the UHS
bysort dhcode: egen hmembfyr = max(hhmembfyr)   // this replaces the HH number of members in the 1st year for all subsequent years
label var hmembfyr "number of HH members in the first yr"
move hmembfyr hhmembfyr
tab hmembfyr if hfyr==1  // number of observations by HH members counts in first year
order hmembfyr, before(year)


***Share of HH MW 0.5<x<1.5***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker if hmembrank==1 & hhmembfyr>1
gen wage2= realwwage*MWworker if hmembrank==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMWfyr2=sum(wage1) if hfyr==1 & (hmembrank==1 | hmembrank==2)
label var hMWfyr "amount of MW of the HH in the 1st year, best 2"
drop wage1 wage2

*Create the denominator of the share 
bysort dhcode: egen htotincfyr=total(realwtotinc) if hfyr==1 & (hmembrank==1 | hmembrank==2)
label var htotincfyr "total non property income in 1st year from 2 best ranked members"

*Creates the HH MW share and extends it to subsequent years
gen hMWshfyr=hMWfyr/htotincfyr
bysort dhcode: egen hMWshare = max(hMWshfyr)   // this replaces the HH MW share for all subsequent years
label var hMWshare "share of HH MW in tot. non property income computed for 2 best ranked first time observed for 0.5<x<1.5"



***Share of HH MW 0.6<x<1.2***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker1 if hmembrank==1 & hhmembfyr>1
gen wage2= realwwage*MWworker1 if hmembrank==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMWfyr1=total(wage1) if hfyr==1 & (hmembrank==1 | hmembrank==2)
label var hMWfyr1 "amount of MW of the HH in the 1st year, 0.6<x<1.6"
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshfyr1=hMWfyr1/htotincfyr
bysort dhcode: egen hMWshare1 = max(hMWshfyr1)   // this replaces the HH MW share for all subsequent years
label var hMWshare1 "share of HH MW in tot. non property income computed for the 2 best ranked first time observed for 0.6<x<1.2"



***Share for Middle Income 1.5<x<3***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MIworker if hmembrank==1 & hhmembfyr>1
gen wage2= realwwage*MIworker if hmembrank==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMWfyrMI=total(wage1) if hfyr==1 & (hmembrank==1 | hmembrank==2)
label var hMWfyrMI "amount of MW of the Mid-Income HH in the 1st year, best 2"
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshfyrMI=hMWfyrMI/htotincfyr
bysort dhcode: egen hMWshareMI = max(hMWshfyrMI)   // this replaces the HH MW share for all subsequent years
label var hMWshareMI "share of MId-Income HH MW in tot. non property income computed for 2 best ranked first time observed for 1.5<x<3"
 
 
 
 
 
***Share of HH MW 0.5<x<1.3***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker13 if hmembrank==1 & hhmembfyr>1
gen wage2= realwwage*MWworker13 if hmembrank==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMWfyr13=total(wage1) if hfyr==1 & (hmembrank==1 | hmembrank==2)
label var hMWfyr13 "amount of MW of the HH in the 1st year, 0.5<x<1.3"
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshfyr13=hMWfyr13/htotincfyr
bysort dhcode: egen hMWshare13 = max(hMWshfyr13)   // this replaces the HH MW share for all subsequent years
label var hMWshare13 "share of HH MW in tot. non property income computed for the 2 best ranked first time observed for 0.5<x<1.3"




***Share of Head and Spouse MW if treated in the first year observed***
*********************************************************************

***For the Share of HH MW 0.5<x<1.5***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker if relhead==1 & hhmembfyr>1
gen wage2= realwwage*MWworker if relhead==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hsMWfyr=total(wage1) if hfyr==1 & (relhead==1 | relhead==2)
drop wage1 wage2

*Create the denominator of the share 
bysort dhcode: egen hstotincfyr=total(realwtotinc) if hfyr==1 & (relhead==1 | relhead==2)
label var hstotincfyr "total non property income of Head and Spouse in 1st year"

*Creates the HH MW share and extends it to subsequent years
gen hsMWshfyr=hsMWfyr/hstotincfyr
bysort dhcode: egen hsMWshare = max(hsMWshfyr)   // this replaces the HH MW share for all subsequent years
label var hsMWshare "share of Head and Spouse MW in tot. non property income first time observed for 0.5<x<1.5"


***For the Share of HH MW 0.5<x<1.2***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker1 if relhead==1 & hhmembfyr>1
gen wage2= realwwage*MWworker1 if relhead==2 & hhmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hsMWfyr1=total(wage1) if hfyr==1 & (relhead==1 | relhead==2)
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hsMWshfyr1=hsMWfyr1/hstotincfyr
bysort dhcode: egen hsMWshare1 = max(hsMWshfyr1)   // this replaces the HH MW share for all subsequent years
label var hsMWshare1 "share of Head and Spouse MW in tot. non property income  first time observed for 0.6<x<1.2"




***Share of all HH MW income in HH TOTAL income if treated in the first year observed***
*********************************************************************************

***For the Share of HH MW 0.5<x<1.5***
bysort dhcode year: egen htotMWfyr=total(realwwage) if hfyr==1 & MWworker==1
bysort dhcode year: egen hhtotMWfyr=max(htotMWfyr) if hfyr==1
replace hhtotMWfyr=0 if hhtotMWfyr==. & hfyr==1 & MWworker==0

*Creates the HH MW share and extends it to subsequent years
gen htotMWshfyr=hhtotMWfyr/hhrealtotinc
bysort dhcode: egen htotMWshare = max(htotMWshfyr)   // this replaces the HH MW share for all subsequent years
label var htotMWshare "share of total HH MW income in tot. HH income first time observed for 0.5<x<1.5"


***For the Share of HH MW 0.5<x<1.2***
bysort dhcode year: egen htotMWfyr1=total(realwwage) if hfyr==1 & MWworker1==1
replace htotMWfyr1=0 if MWworker1==0
gen htotMWshfyr1=htotMWfyr1/hhrealtotinc
bysort dhcode: egen htotMWshare1 = max(htotMWshfyr1)   // this replaces the HH MW share for all subsequent years
label var htotMWshare1 "share of  total HH MW income in tot. HH income first time observed for 0.5<x<1.2"





********************** 7.b ***********************************************************
*** compute the shares S of minimum wage within HH worker is TREATED EACH year *******
**************************************************************************************

***Share of 2 best MW workers in HH if worker treated in each year***
*********************************************************************

***For the Share of HH MW 0.5<x<1.5***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker2 if hmembrank==1 & hmembfyr>1
gen wage2= realwwage*MWworker2 if hmembrank==2 & hmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMW2=total(wage1) if (hmembrank==1 | hmembrank==2)
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshare2=hMW2/b2inc
label var hMWshare2 "share of HH MW in tot. non property income computed if worker treated by year for 0.5<x<1.5"


***For the Share of HH MW 0.5<x<1.5***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker3 if hmembrank==1 & hmembfyr>1
gen wage2= realwwage*MWworker3 if hmembrank==2 & hmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMW3=total(wage1) if (hmembrank==1 | hmembrank==2)
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshare3=hMW3/b2inc
label var hMWshare3 "share of HH MW in tot. non property income computed if worker treated each year for 0.5<x<1.5"


***For the Share of HH MW 0.5<x<1.6***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker5 if hmembrank==1 & hmembfyr>1
gen wage2= realwwage*MWworker5 if hmembrank==2 & hmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hMW5=total(wage1) if (hmembrank==1 | hmembrank==2)
drop wage1 wage2

*Creates the HH MW share and extends it to subsequent years
gen hMWshare5=hMW5/b2inc
label var hMWshare5 "share of HH MW in tot. non property income computed if worker treated each year for 0.6<x<1.2"



***Share of Head and Spouse MW if worker treated in each year ***
****************************************************************
***For the Share of HH MW 0.5<x<1.5***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker3 if relhead==1 & hmembfyr>1
gen wage2= realwwage*MWworker3 if relhead==2 & hmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hsMW3=total(wage1) if (relhead==1 | relhead==2)
drop wage1 wage2

*Creates the HH MW share 
gen hsMWshare3=hsMW3/HSinc
label var hsMWshare3 "share of Head and Spouse MW in tot. non property income if worker treated each year for 0.5<x<1.5"


***For the Share of HH MW 0.6<x<1.6***
*Create the numerator of the share of HH minimum wage income in total income
gen wage1= realwwage*MWworker5 if relhead==1 & hmembfyr>1
gen wage2= realwwage*MWworker5 if relhead==2 & hmembfyr>1
bysort dhcode: replace wage1=wage2 if wage1==.
bysort dhcode year: egen hsMW5=total(wage1) if (relhead==1 | relhead==2)
drop wage1 wage2

*Creates the HH MW share 
gen hsMWshare5=hsMW5/HSinc
label var hsMWshare5 "share of Head and Spouse MW in tot. non property income if worker treated each year for 0.6<x<1.2"



***Share of HH MW income in HH TOTAL income if treated each year observed***
**************************************************************************

***For the Share of HH MW 0.5<x<1.5***
bysort dhcode year: egen htotMW3=total(realwwage) if MWworker3==1
bysort dhcode year: egen hhtotMW3=max(htotMW3)
replace htotMW3=0 if hhtotMW3==. & MWworker3==0
gen htotMWshare3=htotMW3/hhrealtotinc
label var htotMWshare3 "share of  total HH MW income in tot. HH income in each year for 0.5<x<1.5"

***For the Share of HH MW 0.6<x<1.2***
bysort dhcode year: egen htotMW5=total(realwwage) if MWworker5==1
replace htotMW5=0 if MWworker5==0
gen htotMWshare5=htotMW5/hhrealtotinc
label var htotMWshare5 "share of  total HH MW income in tot. HH income in each year for 0.6<x<1.2"



********************* 8. ********************
********* Merge with City dataset ***********
*********************************************

*Merge with city level economic conditions data 
merge m:1 cityid year using 00-11citydata.dta
drop if _merge==2
drop _merge

foreach var of varlist citypop citynatgrpop citygdp citygdpgr citytotfixin citytotlabor citytotempl citysalary citygovinc citygovexp citygovbalance citybigfirms cityemplbigfirms lbcases   {
count if `var'==. & honeobsfyr==0
}
*


*create city control variables: city gov balance as percentage of city gdp, and real fix investment etc.)

gen citygovbalgdp=citygovbalance/citygdp*100
label var citygovbalgdp "city government balance as % of city gdp"

gen realcityfixinv=citytotfixin/cpiindex 
label var realcityfixinv "real total fixed investment in city, base 2002"

gen lnrealcityfixinv=ln(realcityfixinv)
label var lnrealcityfixinv "log of real total fixed investment in city, base 2002"

gen realcitysalary=citysalary/cpiindex
label var realcitysalary "real salary in city, base 2002"

gen lnrealcitysalary=ln(realcitysalary)
label var lnrealcitysalary "log of real salary in city, base 2002"

gen lncityempl=ln(citytotempl)
label var lncityempl "log of number employed in city"

gen realcitygdp=citygdp/cpiindex
label var realcitygdp "real GDP in the city, in levels"

gen lnrealcitygdp=ln(realcitygdp)
label var lnrealcitygdp "log of the level of real GDP in the city"

gen cityunemplrate = (citytotlabor-citytotempl)/citytotlabor
label var cityunemplrate "city unemployment rate"

gen citypopabs= citypop*10000
gen lncitypop=ln(citypopabs)
label var lncitypop "log of city population"

gen lnemplbigfirms=ln(cityemplbigfirms)
label var lnemplbigfirms "log of number employed in city big firms"

gen emplpop=citytotempl/citypop
label var emplpop "employment population ratio"


foreach var of varlist lncitypop citynatgrpop citygdp citygdpgr citytotfixin citytotlabor citytotempl citysalary citygovinc citygovexp citygovbalance citybigfirms lnemplbigfirms lbcases   {
count if `var'==. & honeobsfyr==0
}
*





********************  9. *********************
****  Prepare Variables for  regressions  ****
**********************************************

xtset workerid year
tab year, gen(year)
label var year1 "2002"
label var year2 "2003"
label var year3 "2004"
label var year4 "2005"
label var year5 "2006"
label var year6 "2007"
label var year7 "2008"
label var year8 "2009"


*generate migration dummies and time since migration to city only for those that have migrated
gen migrdum=0 if migration==age
replace migrdum=1 if migrdum==.

gen migrtime=migration if migrdum==1
replace migrtime=0 if migrdum==0
gen migrtime2=migrtime^2

gen migr1=1 if migration<2
replace migr1=0 if migr1==.
gen migr5=1 if migration<6
replace migr5=0 if migr5==.
gen migr10=1 if migration<11
replace migr10=0 if migr10==.
gen migrold=1 if migration>10
replace migrold=0 if migrold==.
gen lnmigration=ln(migrtime)


*generate quadratic terms
gen age2=age^2
gen educ2=educ^2
gen wexper2=wexper^2
gen migration2=migration^2




* Disposable Income, Labor Income, Transfer Income and Other Income

gen hhrealdispinc=(d921+d922+d923+d924)/cpiindex  
label var hhrealdispinc "HH real disposable income, base 2002"

gen hhrealdispincUHS=d920/cpiindex
replace hhrealdispincUHS=. if hhrealdispincUHS<0  
label var hhrealdispincUHS "UHS HH real disposable income , base 2002"

gen hhrealdispincnp=(d921+d922+d923+d924-d9241)/cpiindex  
label var hhrealdispincnp "HH real disposable income net of pension, base 2002"

gen hhrealdispincUHSnp=(d920-d9241)/cpiindex
label var hhrealdispincUHSnp "d920 HH real disposable income net of pensions , base 2002"

gen hhreallabinc=d921/cpiindex
label var hhreallabinc "HH real labor income, base 2002"

gen hhrnetlabinc=(d921-d955)/cpiindex
label var hhreallabinc "HH real net of social security labor income, base 2002"

gen hhrealtrinc=d924/cpiindex
label var hhrealtrinc "HH real transfer income, base 2002"

gen hhrealtrincnp=d924-d9241/cpiindex
label var hhrealtrincnp "HH real transfer net of pensions, base 2002"

gen hhrealgovtrincnp=(d924-(d9241+d9246+d9247+d9248+d9249+d92410))/cpiindex
label var hhrealgovtrincnp "HH real government transfer net of pensions, base 2002"

gen hhreallabtrinc=(d921+d924)/cpiindex
label var hhreallabtrinc "HH real labor and transfer income, base 2002"

gen hhrnetlabtrinc=(d921-d955+d924)/cpiindex
label var hhreallabtrinc "HH real net labor and transfer income, base 2002"

gen hhreallabtrincnp=(d921+d924-d9241)/cpiindex
label var hhreallabtrincnp "HH real labor and transfer net of pension, base 2002"

gen hhrnetlabtrincnp=(d921-d955+d924-d9241)/cpiindex
label var hhrnetlabtrincnp "HH real net labor and transfer net of pension, base 2002"

gen hhrealotherinc=(d922+d923+d924)/cpiindex
label var hhrealotherinc "HH real other income, base 2002"

gen hhrealotherincUHS=(d920-d921)/cpiindex
label var hhrealotherincUHS "HH disp inc UHS - gross labor income, base 2002"

gen hhrealopinc=d922/cpiindex
label var hhrealopinc "HH real operating income, base 2002"

gen hhrealprinc=d923/cpiindex
label var hhrealprinc "HH real property income, base 2002"

gen hhrealpropinc=(d922+d923)/cpiindex
label var hhrealpropinc "HH real property + operating income, base 2002"

gen hhrealhsaleinc=(d93)/cpiindex
label var hhrealhsaleinc "HH real home sale income, base 2002"

gen hhreallendinc=(d94)/cpiindex
label var hhreallendinc "HH real lending income, base 2002"

gen hhrealhsln=(d93+d94)/cpiindex
gen hhrealoinnp=(d9241+d922+d923+d93+d94)/cpiindex 


* Types of transfer income
gen hhrealpension=d9241/cpiindex
gen hhrealsocial=d9242/cpiindex 
gen hhrealdismissal=d9243/cpiindex 
gen hhrealindemnity=d9244/cpiindex 
gen hhrealinsurance=d9245/cpiindex 
gen hhrealsupport=d9246/cpiindex 
gen hhrealdonation=d9247/cpiindex 
gen hhrealrelatives=d9248/cpiindex 
gen hhrealhousing=d9249/cpiindex 
gen hhrealaccounting=d92410/cpiindex 
gen hhrealother=d92411/cpiindex

gen hhrdiUHSnettr=hhrealdispincUHS-hhrealtrinc
gen hhrdiUHSnetpen=hhrealdispincUHS-hhrealpension


*total consumption and total expenditure
gen hhrealcons=d951/cpiindex
label var hhrealcons "HH real consumption, base 2002"  //  foods, clothes, household equipments and service, medical care, transportation and communication, education, culture and entertainment service, sundry goods and services
gen lnhhrealcons = ln(hhrealcons)

gen hhrealexpend=d95/cpiindex  
label var hhrealexpend "HH real expenditure, base 2002" // consumption + expenditures of purchasing house and building, transfer, property and social security expenditure

gen lnhhrealexpend=ln(hhrealexpend)
label var lnhhrealexpend "log of HH real expenditure, base 2002"


*Net savings and debt
gen hhrealsavingnc = hhrealdispinc - hhrealexpend 
label var hhrealsavingnc "LabInc + PropInc + OperatInc + Tansfers - Expenditure"
gen hhrealdebt = -(hhrealsavingnc) if hhrealsavingnc<0
replace hhrealdebt=0 if hhrealdebt==.
gen hhindebted = hhrealdebt>0 if !missing(hhrealdebt)

gen hhrealsavingUHS = (d920/cpiindex) - hhrealexpend + (d955/cpiindex)
label var hhrealsavingUHS "Disp Inc - Expenditure"
gen hhrealdebtUHS = -(hhrealsavingUHS) if hhrealsavingUHS<0
replace hhrealdebtUHS=0 if hhrealdebtUHS==.
gen hhindebtedUHS = hhrealdebtUHS>0 if !missing(hhrealdebtUHS)

gen hhrealsavingdep = (d961+d962+d963+d964+d965+d966)/cpiindex
label var hhrealsavingdep "HH real deposits saved and financial investments" 

gen hhrealsavinginv = (d962+d963+d964+d965+d966)/cpiindex
label var hhrealsavinginv "HH real financial investments" 

*Net Saving including deposits and investments
gen hhrsndi = hhrealsavingnc+hhrealsavingdep
gen hhrsndiUHS = hhrealsavingUHS+hhrealsavingdep

*Net Saving including deposits and investments
gen hhrsni = hhrealsavingnc+hhrealsavinginv
gen hhrsniUHS = hhrealsavingUHS+hhrealsavinginv

*Gross Saving
gen hhrealsavinggr = hhrealdispinc - hhrealexpend + (d955/cpiindex) 
gen hhrealsavinggrUHS = (d920/cpiindex) - hhrealexpend + 2*(d955/cpiindex)

*Gross Saving including deposits and investments
gen hhrsgdi = hhrealsavinggr+hhrealsavingdep
gen hhrsgdiUHS = hhrealsavinggrUHS+hhrealsavingdep

*Gross Saving including deposits and investments
gen hhrsgi = hhrealsavinggr+hhrealsavinginv
gen hhrsgiUHS = hhrealsavinggrUHS+hhrealsavinginv



*create log of HH consumption by type
gen hhperssocseccons=d955/cpiindex
label var hhperssocseccons "HH real personal social security consumption"
gen lnhhperssocseccons = ln(hhperssocseccons)



*Health and Education
*health
gen hhhealthcons=d9514/cpiindex
label var hhhealthcons "HH real health consumption"
gen lnhhhealthcons = ln(hhhealthcons)
gen crhhhealthcons = hhhealthcons^1/3

gen hhhealthcons1=f4/cpiindex
label var hhhealthcons1 "HH real health consumption"
gen lnhhhealthcons1 = ln(hhhealthcons1)
gen crhhhealthcons1 = hhhealthcons1^1/3

*educ
gen hheduccons1=f6/cpiindex
label var hheduccons1 "HH real health consumption"
gen lnhheduccons1 = ln(hheduccons1)
gen crhheduccons1 = hheduccons1^1/3

gen hheduccons=(d9516-(f617_2 + f616_2 + f615_2 + f613_2 + f612_2 + f611_2))/cpiindex
label var hheduccons "HH real education and culture consumption"
gen lnhheduccons = ln(hheduccons)
gen crhheduccons = hheduccons^1/3



*Non-Durables and Services
*food
gen hhfoodcons=e1/cpiindex
label var hhfoodcons "HH real food consumption"
gen lnhhfoodcons = ln(hhfoodcons)
gen crhhfoodcons = hhfoodcons^1/3

*services
gen hhservicescons=(f512+f513+f514+f82)/cpiindex
label var hhservicescons "HH real services consumption"
gen lnhhservicescons = ln(hhservicescons)
gen crhhservicescons = hhservicescons^1/3

*clothing
gen hhclothingcons=f2/cpiindex
label var hhclothingcons "HH real clothing consumption"
gen lnhhclothingcons = ln(hhclothingcons)
gen crhhclothingcons = hhclothingcons^1/3

*services plus clothing
gen hhservicescons1=(hhservicescons+hhclothingcons)
label var hhservicescons1 "HH real non-durables services consumption"
gen lnhhservicescons1 = ln(hhservicescons1)
gen crhhservicescons1 = hhservicescons1^1/3

*gold and silver jewelry Watches
gen hhsoundrygoodscons=f81/cpiindex
label var hhsoundrygoodscons "HH real soundry goods consumption"
gen lnhhsoundrygoodscons = ln(hhsoundrygoodscons)
gen crhhsoundrygoodscons = hhsoundrygoodscons^1/3

*non-durables
gen hhnondurabcons=(hhfoodcons+hhservicescons+hhclothingcons+hhsoundrygoodscons)/cpiindex
label var hhnondurabcons "HH real non-durables consumption"
gen lnhhnondurabcons = ln(hhnondurabcons)
gen crhhnondurabcons = hhnondurabcons^1/3


*Durables
*equipment
gen hhequipmentcons=(f3+f52)/cpiindex
label var hhequipmentcons "HH real house equipment consumption"
gen lnhhequipmentcons = ln(hhequipmentcons)
gen crhhequipmentcons = hhequipmentcons^1/3

*electronics
gen hhelectronicscons=(f617_2 + f616_2 + f615_2 + f613_2 + f612_2 + f611_2)/cpiindex
label var hhelectronicscons "HH real electronics consumption"
gen lnhhelectronicscons = ln(hhelectronicscons)
gen crhhelectronicscons = hhelectronicscons^1/3

*purchase house
gen hhhousecons=d952/cpiindex
label var hhhousecons "HH real housing consumption"
gen lnhhhousecons = ln(hhhousecons)
gen crhhhousecons = hhhousecons^1/3

*transportation
gen hhtranspcons=f511/cpiindex
label var hhtranspcons "HH real transportation consumption"
gen lnhhtranspcons = ln(hhtranspcons)
gen crhhtranspcons = hhtranspcons^1/3

gen hhdurablescons=(hhequipmentcons+hhtranspcons+hhelectronicscons)
label var hhdurablescons "HH real durables consumption"
gen lnhhdurablescons = ln(hhdurablescons)
gen crhhdurablescons = hhdurablescons^1/3

gen hhdurableswithhousing=(hhequipmentcons+hhtranspcons+hhelectronicscons+hhhousecons)
label var hhdurableswithhousing "HH real durables consumption"
gen lndurablesconsnohousing = ln(hhdurableswithhousing)
gen crdurablesconsnohousing = hhdurableswithhousing^1/3



*generate HH ratios
gen workerlabincsh=realwwage/realwtotinc
label var workerlabincsh "worker share of labor income in tot. income"

gen hhrlabincsh=hhreallabinc/hhrealdispinc
label var hhrlabincsh "HH labor income share"

gen hhrpropincsh=hhrealprinc/hhrealdispinc
label var hhrpropincsh "HH property income share"

gen hhrtransfincsh=hhrealtrinc/hhrealdispinc
label var hhrtransfincsh "HH transfer income share"

gen hhrtransfincnpsh=hhrealtrincnp/hhrealdispinc
label var hhrtransfincnpsh "HH transfer income net of pensions share"

gen hhrexpsh=hhrealexpend/hhrealdispinc
label var hhrexpsh "HH tot expenditure share"

gen hhrconssh=hhrealcons/hhrealdispinc
label var hhrconssh "HH consumption share"

gen hhrsavingsh=hhrealsavingnc/hhrealdispinc
label var hhrsavingsh "HH savings share"

gen hhrsavingdepsh=hhrealsavingdep/hhrealdispinc
label var hhrsavingdepsh "HH deposits share"

gen hhrdurablessh=hhdurablescons/hhrealdispinc
label var hhrdurablessh "HH durables cons sh"

gen hhrnondurablessh=hhnondurabcons/hhrealdispinc
label var hhnondurabcons "HH non-durables cons sh"

gen hhrhousingsh=hhhousecons/hhrealdispinc
label var hhrhousingsh "HH housing cons sh"

gen hhrhealthsh=hhhealthcons/hhrealdispinc
label var hhrhealthsh "HH health cons sh"

gen hhreducsh=hheduccons/hhrealdispinc
label var hhreducsh "HH education cons sh"

gen hhrpsssh=hhperssocseccons/hhrealdispinc
label var hhrpsssh "HH pers social sec cons sh"


*Minimum Living Costs
sum hhrealexp, detail
bysort countyid year: egen lncountyminlc = pctile(lnhhrealexpend), p(5)
label var lncountyminlc "minimum living cost of county HHs"
move lncountyminlc lnemplbigfirms

*MW bite
bysort countyid year: egen medhhlabinc = median(hhreallabinc)
gen mwbite = effrealmw/medhhlabinc



*** Construct the Best 2 income ranked members Dataset ****
***********************************************************

gen MWworkerH = MWworker if hmembrank==1
gen MWworkerS = MWworker if hmembrank==2
gen MWworker1H = MWworker1 if hmembrank==1
gen MWworker1S = MWworker1 if hmembrank==2
gen MWworker3H = MWworker3 if hmembrank==1
gen MWworker3S = MWworker3 if hmembrank==2
gen MWworker5H = MWworker5 if hmembrank==1
gen MWworker5S = MWworker5 if hmembrank==2
gen MIworkerH = MIworker if hmembrank==1
gen MIworkerS = MIworker if hmembrank==2
gen MIworker1H = MIworker1 if hmembrank==1
gen MIworker1S = MIworker1 if hmembrank==2
gen HIworkerH = HIworker if hmembrank==1
gen HIworkerS = HIworker if hmembrank==2
gen genderH = gender if hmembrank==1
gen genderS = gender if hmembrank==2
gen maritstatusH = maritstatus if hmembrank==1
gen maritstatusS = maritstatus if hmembrank==2
gen ageH = age if hmembrank==1
gen ageS = age if hmembrank==2
gen age2H = age2 if hmembrank==1
gen age2S = age2 if hmembrank==2
gen educH = educ if hmembrank==1
gen educS = educ if hmembrank==2
gen educ2H = educ2 if hmembrank==1
gen educ2S = educ2 if hmembrank==2
gen wexperH = wexper if hmembrank==1
gen wexperS = wexper if hmembrank==2
gen wexper2H = wexper2 if hmembrank==1
gen wexper2S = wexper2 if hmembrank==2
gen migrH = migrtime if hmembrank==1
gen migrS = migrtime if hmembrank==2
gen migr2H = migrtime2 if hmembrank==1
gen migr2S = migrtime2 if hmembrank==2
gen industryH = industry if hmembrank==1
gen industryS = industry if hmembrank==2
gen occupationH = occupation if hmembrank==1
gen occupationS = occupation if hmembrank==2
gen wtotincH = wtotinc if hmembrank==1
gen wtotincS = wtotinc if hmembrank==2
gen wwageH = wwage if hmembrank==1
gen wwageS = wwage if hmembrank==2

**** Create Best 2 ranked dataset ****
preserve
keep if hmembrank==2
save 2ndrank, replace
restore
keep if hmembrank==1
save 1strank, replace
joinby dhcode year using "2ndrank.dta", update unmatched(both)
keep if _merge==3
drop _merge
save B2_correct,replace
clear



